﻿using SAD_Assignment1.DTO;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace SAD_Assignment1.DAO
{
    public class AccountDAO
    {
        public bool CheckLogin(string email, string pass)
        {            
            bool result = false;
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "SELECT dbo.checkLogin(@email,@pass)";
                    cmd.Parameters.AddWithValue("@email", email);
                    cmd.Parameters.AddWithValue("@pass", pass);
                    result = (bool)cmd.ExecuteScalar();                    
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;
        }

        public bool IsEmailExist(string email)
        {
            bool result = false;
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "SELECT dbo.isAccountExist(@email)";
                    cmd.Parameters.AddWithValue("@email", email);                    
                    result = (bool)cmd.ExecuteScalar();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;
        }

        public int AddNewAccount(string email, string pass, string name, string roles)
        {
            int result = 0;
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "addNewAccount";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;                    
                    cmd.Parameters.AddWithValue("@email", email);
                    cmd.Parameters.AddWithValue("@pass", pass);
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@roles", roles);
                    SqlParameter ID = new SqlParameter();
                    ID.ParameterName = "@ID";
                    ID.SqlDbType = System.Data.SqlDbType.Int;
                    ID.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(ID);
                    cmd.ExecuteNonQuery();
                    result = (int)ID.Value;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;
        }

        //public string getAccountRole(string email)
        //{
        //    string result = null;
        //    try
        //    {
        //        string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

        //        using (var conn = new SqlConnection(connStr))
        //        using (var cmd = conn.CreateCommand())
        //        {
        //            conn.Open();
        //            cmd.CommandText = "SELECT dbo.getAccountRole(@email)";
        //            cmd.Parameters.AddWithValue("@email", email);
        //            result = (string)cmd.ExecuteScalar();
        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        Console.WriteLine(e.Message);
        //    }
        //    return result;
        //}

        public List<AccountDTO> getAccountInfo(string email)
        {
            List<AccountDTO> list = new List<AccountDTO>();
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "SELECT * FROM getAccountInfo(@email)";
                    cmd.Parameters.AddWithValue("@email", email);
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    DataTable table = new DataTable();
                    table.Load(reader);
                    foreach (DataRow row in table.Rows)
                    {
                        int ID = (int)row["ID"];
                        string name = row["name"].ToString();
                        string pass = row["password"].ToString();                                                
                        string roles = row["role_name"].ToString();
                        AccountDTO acc = new AccountDTO(ID, email, name, pass, roles);
                        list.Add(acc);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return list;
        }


        public bool ActiveAccount(string email, int id)
        {
            bool result = false;
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "activeAccount";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@email", email);
                    cmd.Parameters.AddWithValue("@ID", id);
                    SqlParameter res = new SqlParameter();
                    res.ParameterName = "@result";
                    res.SqlDbType = System.Data.SqlDbType.Bit;
                    res.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(res);
                    cmd.ExecuteNonQuery();
                    result = (bool)res.Value;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;

        }

        public List<UserDTO> getUserInfo(int accID)
        {
            List<UserDTO> list = new List<UserDTO>();
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "SELECT * FROM getUserInfo(@accID)";
                    cmd.Parameters.AddWithValue("@accID", accID);
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    DataTable table = new DataTable();
                    table.Load(reader);
                    foreach (DataRow row in table.Rows)
                    {
                        int ID = (int)row["ID"];
                        string email = row["email"].ToString();                        
                        string name = row["name"].ToString();                        
                        string roles = row["role_name"].ToString();
                        int photoCount = (int)row["photoCount"];
                        int photoLikeCount = (int)row["photoLikeCount"];
                        int photoUnlikeCount = (int)row["photoUnlikeCount"];
                        UserDTO acc = new UserDTO(ID,email,name,roles,photoCount,photoLikeCount,photoUnlikeCount);
                        list.Add(acc);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return list;
        }

        public bool UpdateAccount(int accID, string email, string pass, string name, string roleName)
        {
            bool result = false;
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "UpdateAccount";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@accID", accID);
                    cmd.Parameters.AddWithValue("@email", email);
                    cmd.Parameters.AddWithValue("@pass", pass);
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@roleName", roleName);
                    SqlParameter res = new SqlParameter();
                    res.ParameterName = "@result";
                    res.SqlDbType = System.Data.SqlDbType.Bit;
                    res.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(res);
                    cmd.ExecuteNonQuery();
                    result = (bool)res.Value;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return result;
        }

        public List<AccountDTO> getAccountList()
        {
            List<AccountDTO> list = new List<AccountDTO>();
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "SELECT * FROM getAccountList()";                    
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    DataTable table = new DataTable();
                    table.Load(reader);
                    foreach (DataRow row in table.Rows)
                    {
                        int ID = (int)row["ID"];
                        string name = row["name"].ToString();
                        string pass = row["password"].ToString();
                        string roles = row["role_name"].ToString();
                        string email = row["email"].ToString();
                        AccountDTO acc = new AccountDTO(ID, email, name, pass, roles);
                        list.Add(acc);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return list;
        }

        public List<AccountDTO> getAccountByID(int accID)
        {
            List<AccountDTO> list = new List<AccountDTO>();
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["DBSource"].ConnectionString;

                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "SELECT * FROM getAccountByID(@accID)";
                    cmd.Parameters.AddWithValue("@accID", accID);
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    DataTable table = new DataTable();
                    table.Load(reader);
                    foreach (DataRow row in table.Rows)
                    {
                        int ID = (int)row["ID"];
                        string name = row["name"].ToString();
                        string pass = row["password"].ToString();
                        string roles = row["role_name"].ToString();
                        string email = row["email"].ToString();
                        AccountDTO acc = new AccountDTO(ID, email, name, pass, roles);
                        list.Add(acc);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return list;
        }

    }
}